package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import server.database.ConnectionDatabase;

import mockup.MeetingMockup;
import mockup.Mockup;
import mockup.ReservationMockup;
import mockup.UserMockup;

public class MeetingDB implements DatabaseAction {

	private String query;
	private Statement st;

	public MeetingDB() {
		try {
			st = ConnectionDatabase.getConnection().createStatement();			
		} catch (Exception e) {
			System.err.println("AppoitmentDB::Constructor:Exception: " + e.getMessage());
		}
	}

	@Override
	public MeetingMockup get(int id) {
		if(id > -1) {
			query = "select * from " + ConnectionDatabase.getNameDB() + ".meeting" +
			" where meeting_id = " + id;

			ResultSet rs = ConnectionDatabase.get(query, st);
			try {
				ArrayList<Integer> participtsIDs = new ArrayList<Integer>();
				int meeting_id = -1;
				String title = "";
				String description = "";
				String location = "";
				int reservation_id = -1;
				int owner_id = -1;

				while (rs.next()) {
					meeting_id = rs.getInt("meeting_id");
					title = rs.getString("title");
					description = rs.getString("description");
					location = rs.getString("location");
					reservation_id = rs.getInt("reservation_id");
					owner_id = rs.getInt("owner_id");

					String query2 = "select participant_id from " + ConnectionDatabase.getNameDB() + ".meetingparticipant " +
					"where meeting_id = " + meeting_id;
					ResultSet rs2 = ConnectionDatabase.get(query2, st);

					while(rs2.next()) {
						participtsIDs.add(rs2.getInt("participant_id"));
					}
				}

				ReservationMockup res = (ReservationMockup) DataHandler.reservation.get(reservation_id);
				MeetingMockup meeting = new MeetingMockup(meeting_id, title, description, location, owner_id, res, participtsIDs);
				return meeting;

			} catch (SQLException e ) {
				System.err.println("MeetingDB::get:SQLException: " + e.getMessage());
			}
		} else {
			System.err.println("0 > id , søker ikke i database");
		}
		return null;
	}


	@Override
	public int insert(Mockup m) {
		MeetingMockup meeting = (MeetingMockup) m;
		query = "INSERT into " + ConnectionDatabase.getNameDB()+".meeting (" +
		"title, description, location, reservation_id, owner_id) " +
		"values('" + meeting.getTitle() + "','" +
		meeting.getDescription() + "','" + meeting.getLocation() + "'," + 
		meeting.getReservation().getId() + "," + meeting.getOwnerID() + ")";

		int dbID = ConnectionDatabase.insert(query);
		meeting.setId(dbID);

		for (int i = 0; i < meeting.getParticipants().size(); i++) {
			addParticipant(meeting.getId(), meeting.getParticipants().get(i));			
		}

		return dbID;
	}

	@Override
	public void update(Mockup m) {
		MeetingMockup meeting = (MeetingMockup) m;
		if(meeting.getId() > 0) {
			query = "UPDATE " + ConnectionDatabase.getNameDB() + ".meeting SET " +
			"title = '" + meeting.getTitle() + "'," +
			"description = '" + meeting.getDescription() + "'," + 
			"location = '" + meeting.getLocation() + "'," + 
			"reservation_id= " + meeting.getReservation().getId() + "," + 
			"owner_id = " + meeting.getOwnerID() + " " +
			"where meeting_id = " + meeting.getId();

			ConnectionDatabase.update(query, st);

		} else {
			System.err.println("Møte har ikke ID; bruk insert i stedet");
		}
	}

	@Override
	public void delete(Mockup m) {
		MeetingMockup meeting = (MeetingMockup) m;
		if(meeting.getId() > -1) {
			query = "DELETE FROM " + ConnectionDatabase.getNameDB() + ".meeting " +
			"where meeting_id = " + meeting.getId();

			try {
				deleteAllParticipants(meeting);
			} catch (SQLException e) {
				System.err.println("MeetingDB::delete:SQLException: " + e.getMessage());
			}
			ConnectionDatabase.update(query, st);
		}
	}

	public List<MeetingMockup> getAll() {
		List<MeetingMockup> meetings = new ArrayList<MeetingMockup>();
		query = "select * from " + ConnectionDatabase.getNameDB() + ".meeting";
		ResultSet rs = ConnectionDatabase.get(query, st);
		try {
			while (rs.next()) {
				int meeting_id = rs.getInt("meeting_id");
				String title = rs.getString("title");
				String description = rs.getString("description");
				String location = rs.getString("location");
				int reservation_id = rs.getInt("reservation_id");
				int owner_id = rs.getInt("owner_id");

				ArrayList<Integer> participtsIDs = new ArrayList<Integer>();

				String query2 = "select participant_id from " + ConnectionDatabase.getNameDB() + ".meetingparticipant " +
				"where meeting_id = " + meeting_id;

				Statement st2 = ConnectionDatabase.getConnection().createStatement();
				ResultSet rs2 = ConnectionDatabase.get(query2, st2);

				while(rs2.next()) {
					participtsIDs.add(rs2.getInt("participant_id"));
				}

				ReservationMockup res = (ReservationMockup) DataHandler.reservation.get(reservation_id);
				MeetingMockup current = new MeetingMockup(meeting_id, title, description, location, owner_id, res, participtsIDs);
				meetings.add(current);
			}
		} catch (SQLException e ) {
			System.err.println("MeetingDB::getAll:SQLException: " + e.getMessage());
		}
		return meetings;
	}

	public void addParticipant(int meetingid, int userid) {
		String addingP = "INSERT INTO " + ConnectionDatabase.getNameDB() + ".meetingparticipant (" +
		"participant_id, meeting_id ) values ( " +
		userid + "," + meetingid + ")";
		ConnectionDatabase.insert(addingP);
	}

	public void addParticipant(MeetingMockup meeting, UserMockup user) {
		addParticipant(meeting.getId(), user.getId());
	}

	public void removeParticipant(MeetingMockup meeting, UserMockup user) throws SQLException {
		removeParticipant(meeting.getId(), user.getId());
	}

	public void removeParticipant(int meetingid, int userid) {
		Statement st2;
		try {
			st2 = ConnectionDatabase.getConnection().createStatement();
			String removeP = "DELETE FROM " + ConnectionDatabase.getNameDB() + ".meetingparticipant " +
			"where userid = " + userid + " and meetingid = " + meetingid;
			ConnectionDatabase.update(removeP, st2);
		} catch (SQLException e) {
			System.err.println("MeetingDB::removeParticipant:SQLException: " + e.getMessage());
		}
	}

	private void deleteAllParticipants(int meetingid) throws SQLException {
		Statement st2 = ConnectionDatabase.getConnection().createStatement();
		String deleteP = "DELETE FROM " + ConnectionDatabase.getNameDB() + ".meetingparticipant " +
		"where meeting_id = " + meetingid;
		ConnectionDatabase.update(deleteP, st2);
	}

	private void deleteAllParticipants(MeetingMockup meeting) throws SQLException {
		deleteAllParticipants(meeting.getId());
	}

	@Override
	public void emptyTable() {
		query = "TRUNCATE TABLE " + ConnectionDatabase.getNameDB() + ".meeting";
		ConnectionDatabase.update(query, st);
	}
}
